package com.glowdayz.glowmee.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import com.glowdayz.common.DBHandler;
import com.glowdayz.common.DateTime;
import com.glowdayz.common.PageHandler;
import com.glowdayz.glowmee.cmd.SecondCategoryCMD;

/** 2차카테고리의 기능을 구현하는 클래스 */
public class SecondCategoryDAO {
	
	static Logger logger = Logger.getLogger(SecondCategoryDAO.class);
	
	private static SecondCategoryDAO instance = null;
	
	DBHandler db = null;
	
	public static SecondCategoryDAO getInstance() {
		if (instance == null) {
			instance = new SecondCategoryDAO();
		}
		return instance;
	}
	
	public SecondCategoryDAO() {
		db = DBHandler.getInstance();
	}

	/**
	 * 2차카테고리 추가
	 * @return int
	 */
	public int insertSecondCategory(SecondCategoryCMD cmd) throws Exception {
		logger.debug("[insertSecondCategory] ---------- Method Start ----------");
		logger.debug("[insertSecondCategory][PARAM] cmd=" + cmd.toString());
		
		int result = 0;
		
		DBHandler.getInstance().beginTrans();
		
		PreparedStatement pstmt = null;
		
		boolean isEnd = true;
		int cmdSortKey = cmd.getSortKey();
		int num = 0;
		while (isEnd) {
			int temp = 0;
			String sortSql = "SELECT idSecondCategory "
					+ "FROM secondcategory WHERE idFirstCategory=? AND sortKey=? AND idSecondCategory != ?";
			pstmt = DBHandler.getInstance().getPrepareStatement(sortSql);
			
			try {
				pstmt.setInt(1, cmd.getIdFirstCategory());
				pstmt.setInt(2, cmdSortKey);
				pstmt.setInt(3, num);
				ResultSet rs = pstmt.executeQuery();
				
				if (rs != null) {
					if (rs.next()) {
						temp = rs.getInt(1);
					}
					rs.close();
					rs = null;
				}
				pstmt.close();
				pstmt = null;
			} catch (SQLException e) {
				DBHandler.getInstance().rollback();
			} finally {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			}
			
			if (temp > 0) {
				num = temp;
				String editSort = "UPDATE secondcategory SET sortKey=(sortKey+1) WHERE idSecondCategory=?";
				pstmt = db.getPrepareStatement(editSort);
				
				try {
					pstmt.setInt(1, num);
					pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;
				} catch (SQLException e) {
					DBHandler.getInstance().rollback();
				} finally {
					if (pstmt != null) {
						pstmt.close();
						pstmt = null;
					}
				}
				
				cmdSortKey++;
				
			} else {
				isEnd = false;
			}
		}
		
		String sql = "INSERT INTO secondcategory("
				+ "idFirstCategory, secondCategoryText, keyWord, sortKey, isDisplay, create_date"
				+ ") VALUES (?, ?, ?, ?, ?, ?);";
		pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, cmd.getIdFirstCategory());
			pstmt.setString(2, cmd.getSecondCategoryText());
			pstmt.setString(3, cmd.getKeyWord());
			pstmt.setInt(4, cmd.getSortKey());
			pstmt.setInt(5, cmd.getIsDisplay());
			pstmt.setString(6, DateTime.getInstance().getNowDateTimeString());
			
			int count = pstmt.executeUpdate();
			
			if (count > 0) {
				// 자동증가 일련변호 조회
				ResultSet rs = pstmt.getGeneratedKeys();
				if (rs.next()) {
					result = rs.getInt(1);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[insertSecondCategory] " + e.getLocalizedMessage());
			logger.error("[insertSecondCategory] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[insertSecondCategory][RETURN] result=" + result);
		logger.debug("[insertSecondCategory] ---------- Method End ----------");
		
		return result;
	}

	/**
	 * 2차카테고리 수정
	 * @return int
	 */
	public int updateSecondCategory(SecondCategoryCMD cmd) throws Exception {
		logger.debug("[updateSecondCategory] ---------- Method Start ----------");
		logger.debug("[updateSecondCategory][PARAM] cmd=" + cmd.toString());
		
		int result = 0;
		
		DBHandler.getInstance().beginTrans();
		
		PreparedStatement pstmt = null;
		
		boolean isEnd = true;
		int cmdSortKey = cmd.getSortKey();
		int num = 0;
		while (isEnd) {
			int temp = 0;
			String sortSql = "SELECT idSecondCategory "
					+ "FROM secondcategory WHERE idFirstCategory=? AND sortKey=? AND idSecondCategory != ?";
			pstmt = DBHandler.getInstance().getPrepareStatement(sortSql);
			
			try {
				pstmt.setInt(1, cmd.getIdFirstCategory());
				pstmt.setInt(2, cmdSortKey);
				pstmt.setInt(3, num);
				ResultSet rs = pstmt.executeQuery();
				
				if (rs != null) {
					if (rs.next()) {
						temp = rs.getInt(1);
					}
					rs.close();
					rs = null;
				}
				pstmt.close();
				pstmt = null;
			} catch (SQLException e) {
				DBHandler.getInstance().rollback();
			} finally {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			}
			
			if (temp > 0) {
				num = temp;
				String editSort = "UPDATE secondcategory SET sortKey=(sortKey+1) WHERE idSecondCategory=?";
				pstmt = db.getPrepareStatement(editSort);
				
				try {
					pstmt.setInt(1, num);
					pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;
				} catch (SQLException e) {
					DBHandler.getInstance().rollback();
				} finally {
					if (pstmt != null) {
						pstmt.close();
						pstmt = null;
					}
				}
				
				cmdSortKey++;
				
			} else {
				isEnd = false;
			}
		}
		
		String sql = "UPDATE secondcategory "
				+ "SET idFirstCategory=?, secondCategoryText=?, keyWord=?, sortKey=?, isDisplay=?, modified_date=? "
				+ "WHERE idSecondCategory=?";
		pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, cmd.getIdFirstCategory());
			pstmt.setString(2, cmd.getSecondCategoryText());
			pstmt.setString(3, cmd.getKeyWord());
			pstmt.setInt(4, cmd.getSortKey());
			pstmt.setInt(5, cmd.getIsDisplay());
			pstmt.setString(6, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(7, cmd.getIdSecondCategory());
			
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[updateSecondCategory] " + e.getLocalizedMessage());
			logger.error("[updateSecondCategory] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[updateSecondCategory][RETURN] result=" + result);
		logger.debug("[updateSecondCategory] ---------- Method End ----------");
		
		return result;
	}

	/**
	 * 2차카테고리 삭제
	 * @param idSecondCategory		- 2차카테고리 항번
	 * @return int
	 */
	public int deleteSecondCategory(int idSecondCategory) throws Exception {
		logger.debug("[deleteSecondCategory] ---------- Method Start ----------");
		logger.debug("[deleteSecondCategory][PARAM] idSecondCategory=" + idSecondCategory);
		
		int result = 0;
		
		String sql = "DELETE FROM secondcategory "
				+ "WHERE idSecondCategory=?";
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idSecondCategory);
			
			DBHandler.getInstance().beginTrans();
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[deleteSecondCategory] " + e.getLocalizedMessage());
			logger.error("[deleteSecondCategory] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[deleteSecondCategory][RETURN] result=" + result);
		logger.debug("[deleteSecondCategory] ---------- Method End ----------");
		
		return result;
	}

	/**
	 * 2차카테고리 조회
	 * @param idSecondCategory		- 2차카테고리 항번
	 * @return SecondCategoryCMD	- 2차카테고리 객체로 리턴
	 */
	public SecondCategoryCMD getSecondCategory(int idSecondCategory) throws Exception {
		logger.debug("[getSecondCategory] ---------- Method Start ----------");
		logger.debug("[getSecondCategory][PARAM] idSecondCategory=" + idSecondCategory);
		
		SecondCategoryCMD result = null;
		
		String sql = "SELECT idSecondCategory, idFirstCategory, secondCategoryText, keyWord, sortKey, isDisplay, create_date, modified_date "
				+ "FROM secondcategory WHERE idSecondCategory=?";
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idSecondCategory);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				if (rs.next()) {
					int num = rs.getInt(1);
					int idFirstCategory = rs.getInt(2);
					String secondCategoryText = rs.getString(3);
					String keyWord = rs.getString(4);
					int sortKey = rs.getInt(5);
					int isDisplay = rs.getInt(6);
					String create_date = rs.getString(7);
					String modified_date = rs.getString(8);
					result = new SecondCategoryCMD(num, idFirstCategory, secondCategoryText, keyWord, sortKey, isDisplay, create_date, modified_date);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getSecondCategory] " + e.getLocalizedMessage());
			logger.error("[getSecondCategory] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getSecondCategory][RETURN] result=" + result.toString());
		}
		logger.debug("[getSecondCategory] ---------- Method End ----------");
		
		return result;
	}

	/**
	 * 2차카테고리 리스트 조회
	 * @param page		- 페이징처리를 위한 시작페이지번호
	 * @param total		- 페이징처리를 위한 한페이당 출력수
	 * @return			- 전체 2차카테고리를 ArrayList로 리턴
	 */
	public ArrayList<SecondCategoryCMD> getSecondCategoryList(int page, int count, String where) throws Exception {
		logger.debug("[getSecondCategoryList] ---------- Method Start ----------");
		logger.debug("[getSecondCategoryList][PARAM] page=" + page);
		logger.debug("[getFirstCategoryList][PARAM] count=" + count);
		
		ArrayList<SecondCategoryCMD> result = null;
		
		int cnt = db.getCount("secondcategory", "idSecondCategory", where);
		
		if (count == -1) {
			count = cnt;
		}
		
		String sql;
		
		if (where == null) {
			sql = "SELECT idSecondCategory, idFirstCategory, secondCategoryText, keyWord, sortKey, isDisplay, create_date, modified_date "
					+ "FROM secondcategory "
					+ "ORDER BY sortKey "
					+ "LIMIT ?, ?";
		} else {
			sql = "SELECT idSecondCategory, idFirstCategory, secondCategoryText, keyWord, sortKey, isDisplay, create_date, modified_date "
					+ "FROM secondcategory "
					+ "WHERE " + where + " "
					+ "ORDER BY sortKey "
					+ "LIMIT ?, ?";
		}
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		if (cnt == 0) {
			PageHandler.getInstance(page, cnt);
		} else {
			// 페이징 처리 --> 한 페이지에 count개씩, 페이지번호는 10개 출력
			PageHandler.getInstance(page, cnt, count, 10);
		}
		
		try {
			pstmt.setInt(1, PageHandler.getInstance().getStartRec());
			pstmt.setInt(2, PageHandler.getInstance().getPageArticleCount());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<SecondCategoryCMD>();
				
				while(rs.next()) {
					int num = rs.getInt(1);
					int idFirstCategory = rs.getInt(2);
					String secondCategoryText = rs.getString(3);
					String keyWord = rs.getString(4);
					int sortKey = rs.getInt(5);
					int isDisplay = rs.getInt(6);
					String create_date = rs.getString(7);
					String modified_date = rs.getString(8);
					SecondCategoryCMD item = new SecondCategoryCMD(
							num, idFirstCategory, secondCategoryText, keyWord, sortKey, isDisplay, create_date, modified_date);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getSecondCategoryList] " + e.getLocalizedMessage());
			logger.error("[getSecondCategoryList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getSecondCategoryList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getSecondCategoryList] ---------- Method End ----------");
		
		return result;
	}
	
	public ArrayList<SecondCategoryCMD> getSecondCategoryList(int type, int idFirst) throws Exception {
		logger.debug("[getSecondCategoryList] ---------- Method Start ----------");
		
		ArrayList<SecondCategoryCMD> result = null;
		
		String sql = null;
		if (type == 1) {
			sql = "SELECT s.idSecondCategory id, s.idFirstCategory, s.secondCategoryText, s.keyWord, s.sortKey, s.isDisplay, "
					+ "s.create_date, s.modified_date, "
					+ "(SELECT COUNT(*) FROM secondcategorymapping m, product p WHERE p.idProduct=m.idProduct "
					+ "AND p.isDisplay=1 AND id=m.idSecondCategory), "
					+ "(SELECT COUNT(*) FROM secondcategorymapping m, product p WHERE p.idProduct=m.idProduct "
					+ "AND p.isDisplay=1 AND id=m.idSecondCategory "
					+ "AND (DATE_FORMAT(NOW(), '%Y%m%d%H%i%s')-p.create_date)<1000000) "
					+ "FROM secondcategory s, firstcategory f WHERE s.isDisplay=1 AND f.isDisplay=1 AND s.idFirstCategory=? "
					+ "GROUP BY s.idSecondCategory ORDER BY s.sortKey";
		} else if (type == 2) {
			sql = "SELECT s.idSecondCategory id, s.idFirstCategory, s.secondCategoryText, s.keyWord, s.sortKey, s.isDisplay, "
					+ "s.create_date, s.modified_date, "
					+ "(SELECT COUNT(*) FROM secondcategorymapping m, product p, reviewcomment r WHERE m.idPRoduct=p.idProduct "
					+ "AND p.isDisplay=1 AND r.isDisplay=1 "
					+ "AND m.idSecondCategory=id AND p.idProduct=r.idProduct), "
					+ "(SELECT COUNT(*) FROM secondcategorymapping m, product p, reviewcomment r WHERE m.idPRoduct=p.idProduct "
					+ "AND p.isDisplay=1 AND r.isDisplay=1 "
					+ "AND m.idSecondCategory=id AND p.idProduct=r.idProduct AND (DATE_FORMAT(NOW(), '%Y%m%d%H%i%s')-r.create_date)<1000000) "
					+ "FROM secondcategory s, firstcategory f WHERE s.isDisplay=1 AND f.isDisplay=1 AND s.idFirstCategory=? "
					+ "GROUP BY s.idSecondCategory ORDER BY s.sortKey";
		}
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idFirst);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<SecondCategoryCMD>();
				
				while(rs.next()) {
					int idSecondCategory = rs.getInt(1);
					int idFirstCategory = rs.getInt(2);
					String secondCategoryText = rs.getString(3);
					String keyWord = rs.getString(4);
					int sortKey = rs.getInt(5);
					int isDisplay = rs.getInt(6);
					String create_date = rs.getString(7);
					String modified_date = rs.getString(8);
					int productCount = rs.getInt(9);
					int newCount = rs.getInt(10);
					SecondCategoryCMD item = new SecondCategoryCMD(idSecondCategory, idFirstCategory, secondCategoryText, 
							keyWord, sortKey, isDisplay, create_date, modified_date, productCount, newCount);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getSecondCategoryList] " + e.getLocalizedMessage());
			logger.error("[getSecondCategoryList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getSecondCategoryList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getSecondCategoryList] ---------- Method End ----------");
		
		return result;
	}
	
	/*public ArrayList<SecondCategoryCMD> getSecondCategoryReviewList(int idFirstCategory) throws Exception {
		logger.debug("[getSecondCategoryList] ---------- Method Start ----------");
		
		ArrayList<SecondCategoryCMD> result = null;
		
		String sql = "SELECT s.idSecondCategory id, s.idFirstCategory, s.secondCategoryText, s.keyWord, s.sortKey, s.isDisplay, "
				+ "s.create_date, s.modified_date, "
				+ "(SELECT COUNT(*) FROM secondcategorymapping m, product p, reviewcomment r WHERE m.idPRoduct=p.idProduct "
				+ "AND p.isDisplay=1 AND r.isDisplay=1 "
				+ "AND m.idSecondCategory=id AND p.idProduct=r.idProduct) "
				+ "(SELECT COUNT(*) FROM secondcategorymapping m, product p, reviewcomment r WHERE m.idPRoduct=p.idProduct "
				+ "AND p.isDisplay=1 AND r.isDisplay=1 "
				+ "AND m.idSecondCategory=id AND p.idProduct=r.idProduct AND (DATE_FORMAT(NOW(), '%Y%m%d%H%i%s')-r.create_date)<1000000) "
				+ "FROM secondcategory s, firstcategory f WHERE s.isDisplay=1 AND f.isDisplay=1 AND s.idFirstCategory=? "
				+ "GROUP BY s.idSecondCategory ORDER BY s.sortKey";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idFirstCategory);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<SecondCategoryCMD>();
				
				while(rs.next()) {
					int idSecondCategory = rs.getInt(1);
					int num = rs.getInt(2);
					String secondCategoryText = rs.getString(3);
					String keyWord = rs.getString(4);
					int sortKey = rs.getInt(5);
					int isDisplay = rs.getInt(6);
					String create_date = rs.getString(7);
					String modified_date = rs.getString(8);
					int productCount = rs.getInt(9);
					int newCount = rs.getInt(10);
					SecondCategoryCMD item = new SecondCategoryCMD(
							idSecondCategory, num, secondCategoryText, keyWord, sortKey, isDisplay, create_date, modified_date, productCount, newCount);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getSecondCategoryList] " + e.getLocalizedMessage());
			logger.error("[getSecondCategoryList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getSecondCategoryList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getSecondCategoryList] ---------- Method End ----------");
		
		return result;
	}*/
	
}
